-- @owner: @zhanghuan96
-- @date: 2024/11/5
-- @testpoint: GMS_XMLGEN.GETXMLTYPE语法2，查询语句是游标
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_xmlgen;
create extension gms_xmlgen;
set behavior_compat_options = 'bind_procedure_searchpath';
--step1:建表;expect:成功
drop table if exists t_getxmltype0007;
create table t_getxmltype0007("integer" integer,BT_COL3 RAW, BT_COL4 BYTEA);
insert into t_getxmltype0007 values(2,HEXTORAW('DEADBEEF'),E'\\xDEADBEEF');
select * from t_getxmltype0007;
--step2:GMS_XMLGEN.NEWCONTEXT测试，入参语句为游标句柄;expect:成功
DECLARE
CURSOR xc is select * from t_getxmltype0007;
xml_cxt gms_xmlgen.ctxhandle;
xml_output xmltype;
BEGIN
open xc;
xml_cxt := gms_xmlgen.newcontext(xc);
xml_output := gms_xmlgen.getxmltype(xml_cxt);
RAISE INFO 'INFO:XML Output: %', xml_output::text; 
close xc;
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step3:2个参数;expect:成功
DECLARE
CURSOR xc is select * from t_getxmltype0007;
xml_cxt gms_xmlgen.ctxhandle;
xml_output xmltype;
BEGIN
open xc;
xml_cxt := gms_xmlgen.newcontext(xc);
xml_output := gms_xmlgen.getxmltype(xml_cxt,468486);
RAISE INFO 'INFO:XML Output: %', xml_output::text; 
close xc;
gms_xmlgen.closecontext(xml_cxt);
END;
/

--step4:建表;expect:成功
drop table if exists t_getxmltype0007_01;
create table t_getxmltype0007_01(id int, name varchar, job varchar);
drop table if exists t_getxmltype0007_02;
create table t_getxmltype0007_02(id int, age int);
insert into t_getxmltype0007_01 values (1, 'zhang', 'worker'),(2, 'li', 'teacher'),(3, 'wang', 'engineer');
insert into t_getxmltype0007_02 values (1, 20),(2, 30),(3, 40);
--step5:入参语句是游标表达式;expect:成功
DECLARE
CURSOR xc IS SELECT t.age, CURSOR(SELECT name FROM t_getxmltype0007_01 t1 where t1.id = t.id) abc FROM t_getxmltype0007_02 t;
xml_cxt gms_xmlgen.ctxhandle;
xml_output xmltype;
BEGIN
open xc;
xml_cxt := gms_xmlgen.newcontext(xc);
xml_output := gms_xmlgen.getxmltype(xml_cxt);
RAISE INFO 'INFO:XML Output: %', xml_output::text; 
close xc;
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step6:2个参数;expect:成功
DECLARE
CURSOR xc IS SELECT t.age, CURSOR(SELECT name FROM t_getxmltype0007_01 t1 where t1.id = t.id) abc FROM t_getxmltype0007_02 t;
xml_cxt gms_xmlgen.ctxhandle;
xml_output xmltype;
BEGIN
open xc;
xml_cxt := gms_xmlgen.newcontext(xc);
xml_output := gms_xmlgen.getxmltype(xml_cxt,4484787);
RAISE INFO 'INFO:XML Output: %', xml_output::text; 
close xc;
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step7:清理环境;expect:成功
drop table if exists t_getxmltype0007;
drop table if exists t_getxmltype0007_01;
drop table if exists t_getxmltype0007_02;
drop extension if exists gms_xmlgen;    